<%--
  Created by IntelliJ IDEA.
  User: lake
  Date: 2020/12/16
  Time: 17:22
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="utf-8" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.Statement" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>$Title$</title>
    <link href="./static/css/bootstrap.min.css" rel="stylesheet"/>
</head>
<script type="text/javascript" src="./static/jquery.min.js"></script>
<script src="./static/js/bootstrap.js"></script>
<body>
<%
    String qdl = session.getAttribute("qdl").toString();
    String fwq = session.getAttribute("fwq").toString();
    String sjkmc = session.getAttribute("sjkmc").toString();
    String yhm = session.getAttribute("yhm").toString();
    String mm = session.getAttribute("mm").toString();
    Statement ps = null;
    Connection ct = null;
    ResultSet rs = null;
    String url = "jdbc:sqlserver://" + fwq + ";databaseName=ReportServer";
    String user = yhm;  //超级管理员
    String password = mm;
    ;  //密码
    try {
//1.加载驱动
        Class.forName(qdl);
        System.out.println("加载驱动成功！");
        try {
//2.连接
            ct = DriverManager.getConnection(url, user, password);
            ps = (Statement) ct.createStatement();
            rs = ps.executeQuery("select * from sbgl.dbo.hqsjk where name='" + sjkmc + "'");
            if (rs.next()) {
                out.println("您需要新建的数据库已有，不需要新建该数据库了");
                out.print("<script>alert('您还没有登录，请登录...'); </script>");
                ps.executeUpdate("use " + sjkmc);
            } else {
                ps.executeUpdate("create database " + sjkmc);
                System.out.println(sjkmc + "数据库已创建成功");
                out.println(sjkmc + "数据库已创建成功<br>");
                out.println("现在创建设备信息表<br>");
                ps.executeUpdate("use " + sjkmc);
                ps.executeUpdate("insert into sbgl.dbo.hqsjk values('" + sjkmc + "','D:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\ry2008.mdf')");
                ps.executeUpdate("CREATE TABLE [dbo].[sbxxb](\n" +
                        "\t[gsid] [varchar](10) NOT NULL,\n" +
                        "\t[id] [int] IDENTITY(1,1) NOT NULL,\n" +
                        "\t[sbbh] [varchar](20) NOT NULL,\n" +
                        "\t[sbmc] [varchar](20) NULL,\n" +
                        "\t[guige] [varchar](20) NULL,\n" +
                        "\t[shccj] [varchar](20) NULL,\n" +
                        "\t[zbxh] [varchar](20) NULL,\n" +
                        "\t[cpuxh] [varchar](20) NULL,\n" +
                        "\t[ncggdx] [varchar](20) NULL,\n" +
                        "\t[xkxh] [varchar](20) NULL,\n" +
                        "\t[ypdx] [varchar](40) NULL,\n" +
                        "\t[xsqppcc] [varchar](20) NULL,\n" +
                        "\t[dw] [varchar](20) NULL,\n" +
                        "\t[shul] [int] NULL,\n" +
                        "\t[gmrq] [varchar](20) NULL,\n" +
                        "\t[ipdz] [varchar](20) NULL,\n" +
                        "\t[macdz] [varchar](20) NULL,\n" +
                        "\t[bm] [varchar](10) NULL,\n" +
                        "\t[shiyr] [varchar](20) NULL,\n" +
                        "\t[qrcode] [ntext] NULL,\n" +
                        "\t[bfbs] [varchar](2) NULL,\n" +
                        "\t[beiz] [varchar](100) NULL,\n" +
                        " CONSTRAINT [PK_id] PRIMARY KEY CLUSTERED \n" +
                        "(\n" +
                        "\t[id] ASC\n" +
                        ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],\n" +
                        " CONSTRAINT [pk_sbbh] UNIQUE NONCLUSTERED \n" +
                        "(\n" +
                        "\t[sbbh] ASC\n" +
                        ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\n" +
                        ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'公司ID编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'gsid'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序列号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'id'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'设备编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'sbbh'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'设备名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'sbmc'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'规格型号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'guige'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产厂家' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'shccj'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主板型号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'zbxh'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CPU型号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'cpuxh'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'内存规格大小' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'ncggdx'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'显卡型号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'xkxh'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'硬盘大小' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'ypdx'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'显示器品牌尺寸大小' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'xsqppcc'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单位' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'dw'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'shul'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'购买日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'gmrq'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IP地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'ipdz'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'MAC物理地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'macdz'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所在部门名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'bm'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'设备使用人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'shiyr'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'设备二维码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'qrcode'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'报废标识' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'bfbs'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'COLUMN',@level2name=N'beiz'\n");
                ps.executeUpdate("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'设备编号唯一键约束' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sbxxb', @level2type=N'CONSTRAINT',@level2name=N'pk_sbbh'\n");
                ps.executeUpdate("ALTER TABLE [dbo].[sbxxb] ADD  CONSTRAINT [DF_sbxxb_dw]  DEFAULT ('台') FOR [dw]\n");
                ps.executeUpdate("ALTER TABLE [dbo].[sbxxb] ADD  CONSTRAINT [DF_sbxxb_shul]  DEFAULT ((1)) FOR [shul]\n");
                ps.executeUpdate("ALTER TABLE [dbo].[sbxxb] ADD  CONSTRAINT [DF_sbxxb_bfbs]  DEFAULT ((0)) FOR [bfbs]\n");
                ps.executeUpdate("ALTER TABLE [dbo].[sbxxb]  WITH CHECK ADD  CONSTRAINT [FK_sbxxb_sbxxb] FOREIGN KEY([id])\n"
                +"REFERENCES [dbo].[sbxxb] ([id])\n");
                ps.executeUpdate("ALTER TABLE [dbo].[sbxxb] CHECK CONSTRAINT [FK_sbxxb_sbxxb]\n");
                out.println("设备信息表已创建完成<br>");
            }
        } catch (Exception e) {
            out.println("连接数据库失败");
            System.out.println("连接数据库失败！");
        }
    } catch (Exception e) {
        out.println("加载驱动失败！");
        System.out.println("加载驱动失败！");
    }

//    out.println("select * from sbxxb" + "<br>");
%>
</body>
<script type="text/javascript">
    $(document).ready(function () {
    })
</script>
</html>
